package interchange.data;


import interchange.domain.Intercambio;
import interchange.utiles.UIDGenerator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public class JDBCIntercambioDAO implements IIntercambioDAO {
	
	 private Connection conn;

	    IUsuarioDAO udao;

	    IObjetoDAO odao;

	    IServicioDAO sdao;

	    public JDBCIntercambioDAO() {
	        conn = ConnectionManager.getInstance().checkOut();
	        udao = new JDBCUsuarioDAO();
	        odao = new JDBCObjetoDAO();
	        sdao = new JDBCServicioDAO();
	    }

	    protected void finalize() {
	        ConnectionManager.getInstance().checkIn(conn);
	    }


	public void delete(String IntercambioID) {
		
		String sql = "DELETE FROM Intercambio WHERE (OIDIntercambio = ?) ";
        PreparedStatement stmt = null;

        String OIDIntercambio = selectOIDOfIntercambio(IntercambioID);

        if (OIDIntercambio!=null) {
        
        String OIDUsuario = selectOIDOfUsuario(OIDIntercambio);
        String OIDObjeto = selectOIDOfObjeto(OIDIntercambio);
        String OIDServicio = selectOIDOfServicio(OIDIntercambio);

        udao.delete(conn, OIDUsuario);
        odao.delete(conn, OIDObjeto);
        sdao.delete(conn, OIDServicio);

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, IntercambioID);
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        }
    }

	private String selectOIDOfServicio(String oIDIntercambio) {
		
		PreparedStatement stmt = null;
        ResultSet result = null;
        String oidServicio = null;
        String sql = "SELECT * FROM Intercambio WHERE (OIDIntercambio = ? ) ";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, oIDIntercambio);

            result = stmt.executeQuery();

            result.next();
            oidServicio = result.getString("OIDServicio");
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return oidServicio;
	}
	
	
    private String selectOIDOfUsuario(String oIDIntercambio) {
		
		PreparedStatement stmt = null;
        ResultSet result = null;
        String oidUsuario = null;
        String sql = "SELECT * FROM Intercambio WHERE (OIDIntercambio = ? ) ";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, oIDIntercambio);

            result = stmt.executeQuery();

            result.next();
            oidUsuario = result.getString("OIDUsuario");
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return oidUsuario;
	}
    
    
    
    
private String selectOIDOfObjeto(String oIDIntercambio) {
		
		PreparedStatement stmt = null;
        ResultSet result = null;
        String oidObjeto = null;
        String sql = "SELECT * FROM Intercambio WHERE (OIDIntercambio = ? ) ";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, oIDIntercambio);

            result = stmt.executeQuery();

            result.next();
            oidObjeto = result.getString("OIDObjeto");
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return oidObjeto;
	}

  private String selectOIDOfIntercambio(String oIDIntercambio) {
	
	PreparedStatement stmt = null;
    ResultSet result = null;
    String oidIntercambio = null;
    String sql = "SELECT * FROM Intercambio WHERE (OIDIntercambio = ? ) ";

    try {
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, oIDIntercambio);

        result = stmt.executeQuery();

        result.next();
        oidIntercambio = result.getString("OIDIntercambio");
    } catch (SQLException e) {
        System.out.println("Message: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("ErrorCode: " + e.getErrorCode());
    } finally {
        try {
            if (result != null) {
                result.close();
            }

            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
        }
    }

    return oidIntercambio;
}






	public void insertIntercambio(Intercambio i) {
		// inserto usuario
		
		String oidu = UIDGenerator.getInstance().getKey();
        udao.insert(conn, oidu, i.getU());
        udao.insert(conn, oidu, i.getUsuarioDeIntercambio());
        
        // inserto objeto
        
        String oido = UIDGenerator.getInstance().getKey();
        odao.insert(conn, oido, i.getO());
        
        //inserto servicio
        
        String oids = UIDGenerator.getInstance().getKey();
        sdao.insert(conn, oids, i.getS());
        
        //inserto intercambio
        String oidi = UIDGenerator.getInstance().getKey();
        PreparedStatement stmt = null;
        String sql = "INSERT INTO Intercambio ( OIDIntercambio, OIDUsuario, OIDServicio, OIDObjeto,IntercambioID, fecha, precio, estado) VALUES (?, ?, ?, ?, ?, ?, ?) ";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, oidi);
            stmt.setString(2, oidu);
            stmt.setString(3, oids);
            stmt.setString(3, oido);
            stmt.setString(4, i.getIntercambioID());
            stmt.setLong(5,i.getFecha().getTime());
            stmt.setDouble(6, i.getPrecio());
            stmt.setBoolean(7, i.getEstado());

            stmt.executeUpdate();
            
            


        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
    }



	
	public List<Intercambio> selectAllIntercambios() {
	        PreparedStatement stmt = null;
	        ResultSet result = null;
	        List<Intercambio> li = new LinkedList();
	        String sql = "SELECT * FROM Intercambio";

	        try {
	            stmt = conn.prepareStatement(sql);
	            result = stmt.executeQuery();

	            while (result.next()) {
	                Intercambio i = new Intercambio();
	                
	                i.setIntercambioID(result.getString("intercambioID"));
	                i.setFecha(new Date(result.getLong("fecha")));
	                i.setPrecio(result.getDouble("precio"));
	                i.setEstado(result.getBoolean("estado"));

	                String oidu = result.getString("OIDUsuario");
	                String oids = result.getString("OIDServicio");
	                String oido = result.getString("OIDObjeto");
	                

	                //get Usuario y usuario de Intercambio
	                i.setU(udao.select(conn, oidu));
	                i.setUsuarioDeIntercambio(udao.select(conn, oidu));

	                //get Servicio
	                i.setS(sdao.select(conn, oids));

	                //get Objeto
	                i.setO(odao.select(conn, oido));

	                li.add(i);
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }

	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }

	        return li;
	    }
	
}

        
        
		